﻿using System;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;

namespace Utilities.Database
{
 public   class Helper
    {
        /// <summary>
        /// Get ConnectionString In Webconfig
        /// </summary>
        // public static string connectionString { get; set; }
     public string connectionString = ConfigurationManager.ConnectionStrings["ConnectionStringSSO"].ConnectionString;
        /// <summary>
        /// Create a new connection with open state
        /// </summary>
        /// <returns></returns>
        public SqlConnection CreateConnection()
        {
            SqlConnection cnn = new SqlConnection(connectionString);
            cnn.Open();
            return cnn;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="inputString"></param>
        /// <returns></returns>
      
        //public string Encrypte(string inputString)
        //{
        //    return FormsAuthentication.HashPasswordForStoringInConfigFile(inputString.Trim(), "MD5");
        //}
        /// <summary>
        /// Replace ' with '' (use in Sql string)
        /// </summary>        
        /// <param name="text">Text value</param>
        public static string SqlString(string text)
        {
            if (text == null || text.Length == 0) return "''";
            return "N'" + text.Replace("'", "''") + "'";
        }
        /// <summary>
        /// Get the DbDataSet of the Sql string
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql)
        {
            using (SqlConnection cnn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(sql, cnn);
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                try
                {
                    da.Fill(ds);
                    cnn.Close();
                    return ds;
                }
                catch (Exception ex)
                {
                    cnn.Close();
                    return null;
                }
            }
        }
        /// <summary>
        /// Return Dataset With StoredProcedure And Prams
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="StringParams"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string StoredProcedure, params SqlParameter[] StringParams)
        {
            using (SqlConnection cnn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(StoredProcedure, cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                try
                {
                    cmd.Parameters.AddRange(StringParams);
                    da.Fill(ds);
                    cnn.Close();
                    return ds;
                }
                catch (Exception ex)
                {
                    cnn.Close();
                    return null;
                }
            }
        }
        /// <summary>
        /// Get the DbDataReader of the Sql string
        /// </summary>    
        /// <param name="sql">Sql String</param>
        /// 
        public SqlDataReader GetDataReader(string sql)
        {
            SqlConnection cnn = CreateConnection();
            SqlCommand cmd = new SqlCommand(sql, cnn);
            cmd.CommandType = CommandType.Text;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            try
            {
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;

            }
            catch (Exception ex)
            {
                cnn.Close();
                return null;
            }
        }
        /// <summary>
        /// Return DataTable With StoreeProcedure And Params
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="StringParams"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string StoredProcedure, params SqlParameter[] StringParams)
        {
            using (SqlConnection cnn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(StoredProcedure, cnn);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                try
                {
                    cmd.Parameters.AddRange(StringParams);
                    da.Fill(dt);
                    cnn.Close();
                    return dt;
                }
                catch (Exception ex)
                {
                    cnn.Close();
                    return null;
                }
            }
        }
        public DataTable GetDataTable(string Sql)
        {
            using (SqlConnection cnn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(Sql, cnn);
                cmd.CommandType = CommandType.Text;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataTable dt = new DataTable();
                try
                {
                    da.Fill(dt);
                    cnn.Close();
                    return dt;
                }
                catch (Exception ex)
                {
                    cnn.Close();
                    return null;
                }
            }
        }
        /// <summary>
        /// Get ExecuteNoneQuery Store Insert,Delete,Update With Param
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="StringParams"></param>
        /// <returns></returns>
        public bool GetExecuteNonQueryReturnBool(string StoredProcedure, params SqlParameter[] StringParams)
        {
            using (SqlConnection cnn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(StoredProcedure, cnn);
                cmd.Parameters.AddRange(StringParams);
                cmd.CommandType = CommandType.StoredProcedure;
                int result = cmd.ExecuteNonQuery();
                cnn.Close();
                if (result == 1)
                    return true;
                else
                    return false;
            }
        }
        /// <summary>
        /// Get ExecuteNoneQuery Store Insert,Delete,Update With Param
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="StringParams"></param>
        /// <returns></returns>
        public bool GetExecuteNonQueryReturnBool(string sql)
        {
            using (SqlConnection cnn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(sql, cnn);
                cmd.CommandType = CommandType.Text;
                int result = cmd.ExecuteNonQuery();
                cnn.Close();
                if (result == 1)
                    return true;
                else
                    return false;
            }
        }

        /// <summary>
         /// return int when get ExecuteNonQuery with string sql
         /// </summary>
         /// <param name="sql"></param>
         /// <returns></returns>
        public int GetExecuteNonQueryReturnInt(string sql)
        {
            using (SqlConnection cnn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(sql, cnn);
                cmd.CommandType = CommandType.Text;
                int result = cmd.ExecuteNonQuery();
                cnn.Close();
                return result;
            }
        }

        /// <summary>
        /// Return int when you get ExecuteNonQuery with StoredProcedure
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="StringParams"></param>
        /// <returns></returns>
        public int GetExecuteNonQueryReturnInt(string StoredProcedure, params SqlParameter[] StringParams)
        {
            using (SqlConnection cnn = CreateConnection())
            {
                SqlCommand cmd = new SqlCommand(StoredProcedure, cnn);
                cmd.Parameters.AddRange(StringParams);
                cmd.CommandType = CommandType.StoredProcedure;
                int result = cmd.ExecuteNonQuery();
                cnn.Close();
                return result;
            }
        }

        /// <summary>
        /// Get DataReader With Param
        /// </summary>
        /// <param name="StoredProcedure"></param>
        /// <param name="StringParam"></param>
        /// <returns></returns>
        public SqlDataReader GetDataReader(string StoredProcedure, params SqlParameter[] StringParam)
        {
            SqlConnection cnn = CreateConnection();
            SqlCommand cmd = new SqlCommand(StoredProcedure, cnn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            cmd.Parameters.AddRange(StringParam);
            try
            {
                SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dr;
            }
            catch (Exception ex)
            {
                cnn.Close();
                return null;
            }
        }
    }
}
